using System;
using System.Collections;

namespace Connection
{

    public class MySQL
	{
		private string sql;
		private string tableName ="";
		private System.Data.DataTable table = new System.Data.DataTable();
		private MySql.Data.MySqlClient.MySqlConnection cn = new MySql.Data.MySqlClient.MySqlConnection();
		private MySql.Data.MySqlClient.MySqlDataAdapter dataAdapter=new MySql.Data.MySqlClient.MySqlDataAdapter();
		private MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand();
		
        private int tableIterator = 0;
		private bool tableOver = false;

        private Hashtable sqlCommands = new Hashtable();
		private int countSQL = 0;
		private bool isConnectionShared = false;
		private string errMsg ;

        public string ErrorMessage
        {
            get { return errMsg; }
        }

		public MySQL(ref MySql.Data.MySqlClient.MySqlConnection sharedCn)
		{
			try
			{
                cn = sharedCn;
                if (cn.State != System.Data.ConnectionState.Open) cn.Open();
                isConnectionShared = true;
			}
			catch
			{
			}
         
		}

        public MySQL(MySql.Data.MySqlClient.MySqlConnection sharedCn)
        {
            try
            {
                cn = sharedCn;
                if (cn.State != System.Data.ConnectionState.Open) cn.Open();
                isConnectionShared = true;
            }
            catch
            {
            }

        }

        public MySQL(string dataSource, string userID, string password, string database)
		{
			try
			{
                cn = new MySql.Data.MySqlClient.MySqlConnection("Server=" + dataSource + ";Database=" + database + ";user id=" + userID + ";password=" + password + ";pooling=False;");				
                cn.Open();
			}
			catch (Exception ex)
			{
				
				errMsg=ex.Message;
			}
         
		}

		 public void Dispose()
		{
			try
			{

				if (isConnectionShared == false)
				{
				    cn.Dispose();
                    cn = null;
				}                  

				table.Reset();
				table = null;
				dataAdapter.Dispose();
				dataAdapter = null;
				command.Dispose();
				command = null;	
				
			}

			catch
			{
			
			}
		}

        public int GetLastInsertID()
        {
            string sql = "select last_insert_id()";
            this.SetSQL(sql, "");
            if (!this.IsTableOver())
            {
                return Convert.ToInt32(this.TableGetValue("last_insert_id()"));
            }
            else
            {
                return -1;
            }
        }

		public bool SetSQL (string query,string tbName)
		{
			try
			{
				sql = query;
				tableName = tbName;
				ExecuteSelectQuery();
				return true;
			}
			catch
			{
				return false;
			}
		}

        public bool SetSQL(string query)
        {
            try
            {
                sql = query;
                ExecuteSelectQuery();
                return true;
            }
            catch
            {
                return false;
            }
        }

		public int GetRowCount()
		{
			return table.Rows.Count;
		}

		public System.Data.DataRow GetRowModel()
		{
			return table.NewRow();
		}

		public bool AddRow(System.Data.DataRow rw)
		{
			try
			{
				TableInsertRow(rw);
				table.Rows.Add(rw);
				return true;
			}
			catch
			{
				return false;
			}
		}

		public System.Collections.ArrayList GetSQLCOMMANDS()
		{
			try
			{

				System.Collections.ArrayList al = new ArrayList();

				for (int i=0;i<=countSQL;i++)
				{
					al.Add(sqlCommands[i]);
				}
				return al;
			}

			catch
			{
				return null;
			}
		}

		public bool TableMoveNext()
		{
			try
			{
				if (table.Rows.Count-1 > tableIterator)
				{
					tableIterator ++;
					tableOver = false;

				}
				else tableOver=true;
				return true;
			}
			catch
			{
				return false;
			}
		}

		public bool TableMovePrevious()
		{
			try
			{

				if (table.Rows.Count-1 > tableIterator)
				{
					tableIterator --;
					tableOver = false;

				}				
				return true;
			}
			catch
			{
				return false;
			}
		}

		public bool TableMoveFirst()
		{
			try
			{
				tableIterator = 0;
				if (table.Rows.Count > 0)tableOver = false ;
				else tableOver = true;
				return true;
			}
			catch
			{
				return false;
			}
		}

		public bool TableMoveLast()
		{
			try
			{
				
				if (table.Rows.Count > 0)
				{
					tableIterator = table.Rows.Count - 1;
					tableOver = false;
				}
				else tableOver = true;
			    return true;
			}
			catch
			{
				return false;
			}
		}

		public object TableGetValueReplaceNull(string columnName)
		{
			try
			{
				if (table.Rows[tableIterator][columnName]==System.DBNull.Value)
				{
					Type colType =  table.Columns[columnName].DataType;
					if (colType.Equals(typeof(string))) return " ";
					if (colType.Equals(typeof(DateTime))) return new DateTime(1900, 1, 1);
					if (colType.Equals(typeof(bool))) return  false;
					if (colType.Equals(typeof(double))) return  0;					
					return null;
				}
				else
				{
					return table.Rows[tableIterator][columnName];
				}

			}
			catch
			{
				return null;
			}	
		}

		public object TableGetValue(string columnName)
		{
			try
			{
				return table.Rows[tableIterator][columnName];
			}
			catch
			{
				return null;
			}

		}

		public bool TableChangeValue(string columnName, object val)
		{
			try
			{
	            TableUpdateRow(val, columnName);
	            table.Rows[tableIterator][columnName] = val;
				return true;
			}
			catch
			{
				return false;
			}
		}
		
		public bool TableClear()
		{
			try
			{
				if (table.Rows.Count>0)
				{
					while (table.Rows.Count !=0)
					{
						TableDeleteRow();
					}
				}
				return true;
			}
			catch
			{
				return false;
			} 
		}

		
		public bool TableDeleteRow()
		{
			try
			{

				string strTemp = "DELETE FROM " + tableName + " WHERE ";
				countSQL+=1;
				
				for (int i=0;i<table.Columns.Count;i++)
				{
					if (table.Rows[tableIterator][i]!=DBNull.Value)
					{
						if (Convert.ToString(table.Rows[tableIterator][i]).Trim() !="")
						{
							switch (table.Columns[i].DataType.Name.ToLower())
							{
								case "string":
									strTemp += "[" + table.Columns[i].ColumnName + "] LIKE '" + table.Rows[tableIterator][i] + "'";
									break;
								case "datetime":
									strTemp += "[" + table.Columns[i].ColumnName + "]='" + table.Rows[tableIterator][i] + "'";
									break;
								case "double":
									strTemp += "[" + table.Columns[i].ColumnName + "]=" + Convert.ToString(table.Rows[tableIterator][i]).Replace(",", ".") + "";
									break;
								case "integer":
									strTemp += "[" + table.Columns[i].ColumnName + "]=" + table.Rows[tableIterator][i] + "";
									break;
								default:
									strTemp += "[" + table.Columns[i].ColumnName + "]=" + table.Rows[tableIterator][i] + "";
									break;

							}
							if (i < table.Columns.Count - 1) strTemp += " AND ";

						}
					}
					
					if (strTemp.Substring(strTemp.Length-4,3) == "AND") strTemp = strTemp.Substring(0,strTemp.Length-5);
					if (strTemp.Substring(strTemp.Length - 5, 4)=="AND ".ToUpper().Trim())  strTemp = strTemp.Substring(0,strTemp.Length-5);
					sqlCommands.Add(countSQL, strTemp);
					table.Rows.RemoveAt(tableIterator);
					if (tableIterator > table.Rows.Count - 1) tableIterator = table.Rows.Count - 1;
				}
				return true;
			}
			catch
			{
				return false;
			}
			   
		}

		public bool TableUpdateRow(object changedValue, string colName )
		{
			try
			{
				string  strTemp = "UPDATE " + tableName + " SET [" + colName + "]";
				switch (table.Columns[colName].DataType.Name.ToLower())
				{
					case "string":
						  strTemp += "='" + Convert.ToString(changedValue) + "'";
						break;
					case "datetime":
						  strTemp += "='" + Convert.ToDateTime(changedValue).ToString() + "'";
						break;
					case "double":
						 strTemp += "=" + Convert.ToDouble(changedValue).ToString();
						break;
					case "integer":
						strTemp += "=" + Convert.ToInt32(changedValue).ToString();
						break;
					default:
						strTemp += "=" + Convert.ToString(changedValue);
						break;

				}
				
				strTemp += " WHERE ";
				countSQL++;
				
				for (int i=0;i<table.Columns.Count;i++)
				{
					if (table.Rows[tableIterator][i]!=DBNull.Value)
					{
						if (Convert.ToString(table.Rows[tableIterator][i]).Trim() !="")
						{
							switch (table.Columns[i].DataType.Name.ToLower())
							{
								case "string":
									strTemp += "[" + table.Columns[i].ColumnName + "] LIKE '" + Convert.ToString(table.Rows[tableIterator][i]) + "'";
									break;
								case "datetime":
									strTemp += "[" + table.Columns[i].ColumnName + "]='" + Convert.ToDateTime(table.Rows[tableIterator][i]).ToString() + "'";
									break;
								case "double":
									strTemp += "[" + table.Columns[i].ColumnName + "]=" + Convert.ToString(table.Rows[tableIterator][i]);
									break;
								case "integer":
									strTemp += "[" + table.Columns[i].ColumnName + "]=" + Convert.ToString(table.Rows[tableIterator][i]);
									break;
								default:
									strTemp += "[" + table.Columns[i].ColumnName + "]=" + Convert.ToString(table.Rows[tableIterator][i]);
									break;
							}
							if (i < table.Columns.Count - 1) strTemp += " AND ";
						}
					}

				}
				if (strTemp.Substring(strTemp.Length-4,3) == "AND") strTemp = strTemp.Substring(0,strTemp.Length-5);            
				sqlCommands.Add(countSQL, strTemp);
				if (tableIterator > table.Rows.Count - 1) tableIterator = table.Rows.Count - 1;


				return true;
			}
			catch 
			{
				return false;
			}
		
		}

		public bool TableInsertRow(System.Data.DataRow row)
		{
			try
			{
				string strTemp = "INSERT INTO " + tableName + " VALUES(";
				countSQL ++;
			
				for (int i=0;i<table.Columns.Count;i++)
				{
					if (row[i]!=DBNull.Value)
					{										
						switch (table.Columns[i].DataType.Name.ToLower())
						{
							case "string":
								strTemp += "'" + row[i].ToString() + "'";
								break;
							case "datetime":
								strTemp += "'" + row[i].ToString() + "'";
								break;
							case "double":
								strTemp += row[i].ToString();
								break;
							case "integer":
								strTemp += row[i].ToString();
								break;
							default:
								strTemp += row[i].ToString();
								break;
						}
					}
					if (i < table.Columns.Count - 1) strTemp += " , ";
				}
				strTemp += ")";
				strTemp = strTemp.Replace(",  ,", ",'',");
				sqlCommands.Add(countSQL, strTemp);
				if (tableIterator > table.Rows.Count - 1) tableIterator = table.Rows.Count - 1;
			
				return true;
			}
			catch
			{
				return false;
			}

           
		}
		
		public bool TableUpdateDatabase()
		{
			try
			{
				MySql.Data.MySqlClient.MySqlCommand cmdTemp;
				for (int i=0; i<=countSQL;i++)
				{
					cmdTemp=new MySql.Data.MySqlClient.MySqlCommand(Convert.ToString(sqlCommands[i]),cn);
					cmdTemp.ExecuteNonQuery();
					cmdTemp.Dispose();
				}
				sqlCommands.Clear();
				countSQL = 0;

				return true;
			}
			catch
			{
				return false;
			}
		}

		private bool ExecuteSelectQuery()
		{
			try
			{
				if (sql==null) return false;
				tableOver = false;
				tableIterator=0;
				command = null;
				dataAdapter = null;
				table = null;
				command = new MySql.Data.MySqlClient.MySqlCommand(sql,cn);
				dataAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter(command);
				table = new System.Data.DataTable();
				dataAdapter.Fill(table);
				if (table.Rows.Count ==0) tableOver=true;
				return true;
			}
			catch (Exception ex)
			{
				errMsg=ex.Message;
				return false;
			}
		}
		
		public bool TableDeleteRows(string columnName, object criterium)
		{
			try
			{
				System.Collections.ArrayList rowsToBeDeleted = new ArrayList();
				for (int i=0;i<table.Rows.Count;i++)
				{
					if (Convert.ToString(table.Rows[i][columnName])==Convert.ToString(criterium))
					{
                        rowsToBeDeleted.Add(i);
					}
				}
				while (rowsToBeDeleted.Count >0)
				{
					TableMoveFirst();
					for (int i=0;i<Convert.ToInt32(rowsToBeDeleted[0]);i++)
					{
						TableMoveNext();
					}
					TableDeleteRow();
					rowsToBeDeleted.RemoveAt(0);
					for (int i=0;i<rowsToBeDeleted.Count;i++)
					{
						rowsToBeDeleted[i]=Convert.ToInt32(rowsToBeDeleted[i])-1;
					}
				}
			
				return true;
			}
			catch
			{
				return false;
			}
		}

		public bool IsTableOver()
		{
            if (GetRowCount() == 0) return true;
			else return tableOver;
		}

		public System.Data.DataTable ReturnTable()
		{
			return table;
		}


		public string[] GetTableNames()
		{
			try
			{
				string sql = "SHOW TABLES";
				System.Collections.ArrayList alDetails = new ArrayList();
				int i=0;
				SetSQL(sql,"");
				while (!IsTableOver())
				{

					alDetails.Add(Convert.ToString(table.Rows[i][0]));
					i++;
					TableMoveNext();
				}
				string[] tableNames = (string[]) alDetails.ToArray(typeof(string));
				return tableNames;
			}
			catch
			{
				return null;
			}
		}

		public bool EmptyDBTables()
		{
			try
			{
				string[] tbNames = GetTableNames();

				for (int i=0;i<=tbNames.GetUpperBound(0);i++)
				{
					if (!ExecuteNonQuery("DELETE FROM " +tbNames )) return false;
				}
				return true;
			}
            catch (Exception ex)
			{
                errMsg = ex.Message;
                return false;
			}
		}

		public int GetCurrentRowNumber()
		{
            return tableIterator;
		}
		
		public bool ExecuteNonQuery (string sql)
		{
			try
			{
				MySql.Data.MySqlClient.MySqlCommand cmdTemp = new MySql.Data.MySqlClient.MySqlCommand(sql, cn);
				cmdTemp.ExecuteNonQuery();
				cmdTemp.Dispose();
				return true;
			}
			catch (Exception ex)
			{
                errMsg = ex.Message;
				return false;
			}
		}

		public MySql.Data.MySqlClient.MySqlConnection ShareConnection()
		{
			return cn;
		}


	}
}
